#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into hive.ol_edu_dws.dws_intent_daycount
with tmp as (select
        intent_id,
        create_date_time,
        update_date_time,
        intent_deleted,
        customer_id,
        origin_type,
        "year",
        "month",
        "day",
        area,
        clue_id,
        customer_relationship_id,
        clue_state,
        itcast_school_id,
        itcast_school,
        itcast_subject_id,
        itcast_subject,
        origin_channel,
        employee_id,
        department_id,
        department_name,
        parent_id,
        row_number() over(partition by intent_id) as intent_rn
from hive.ol_edu_dwb.dwb_intent_detail)

select
        "day",
        origin_type,
        clue_state,
        area,
        itcast_subject_id,
        itcast_subject,
        itcast_school_id,
        itcast_school,
        origin_channel,
        department_id,
        department_name,

       case when grouping(area)=0 then 'area'
             when grouping(itcast_subject_id)=0 then 'itcast_subject'
             when grouping(itcast_school_id)=0 then 'itcast_school'
             when grouping(origin_channel)=0 then 'origin_channel'
             when grouping(department_id)=0 then 'department'
             when grouping(origin_type)=0 then 'origin_type'
             when grouping(clue_state)=0 then 'clue_state'
             else 'all' end as group_type,

    --意向用户个数指标
        case when grouping(area)=0
            then count(if(intent_rn=1 and area is not null  ,intent_id,null))
            when grouping(itcast_subject_id,itcast_subject)=0
            then count(if(intent_rn=1 and itcast_subject_id is not null  ,intent_id,null))
            when grouping(itcast_school_id,itcast_school)=0
            then count(if(intent_rn=1 and itcast_school_id is not null  ,intent_id,null))
            when grouping(origin_channel)=0
            then count(if(intent_rn=1 and origin_channel is not null  ,intent_id,null))
            when grouping(department_id,department_name)=0
            then count(if(intent_rn=1 and area is not null  ,intent_id,null))
            when grouping(origin_type)=0
            then count(if(intent_rn=1 and origin_type is not null  ,intent_id,null))
            when grouping(clue_state)=0
            then count(if(intent_rn=1 and clue_state is not null  ,intent_id,null))
            when grouping(day)=0
            then count(if(intent_rn=1 ,intent_id,null))
            else null end as intent_avl_count
from tmp
group by
grouping sets (
        (day,origin_type,clue_state),
        (day,area,origin_type,clue_state),
        (day,itcast_subject_id,itcast_subject,origin_type,clue_state),
        (day,itcast_school_id,itcast_school,origin_type,clue_state),
        (day,origin_channel,origin_type,clue_state),
        (day,department_id,department_name,origin_type,clue_state)
);




insert into hive.ol_edu_dws.dws_clue_hourcount
with tmp as (select
        create_date_time,
        origin_type,
        clue_state,
        clue_id,
        appeal_status,
        "day",
        "hour",
        row_number() over(partition by clue_id) as clue_rn
from hive.ol_edu_dwb.dwb_clue_detail)

select
        "hour",
        origin_type,
        clue_state,

       case when grouping(origin_type)=0 then 'origin_type'
               when grouping(clue_state)=0 then 'clue_state'
               else 'all' end as group_type,

    --有效线索
        case when grouping(origin_type)=0
            then count(if(appeal_status='有效' and clue_rn=1 and origin_type is not null,clue_id,null))
            when grouping(clue_state)=0
            then count(if(appeal_status='有效' and clue_rn=1 and clue_state is not null,clue_id,null))
            when grouping(hour)=0
            then count(if(appeal_status='有效' and clue_rn=1 ,clue_id,null))
            else null end as clue_avl_count,
       --总线索
        case when grouping(origin_type)=0
            then count(if(clue_rn=1 and origin_type is not null,clue_id,null))
            when grouping(clue_state)=0
            then count(if(clue_rn=1 and clue_state is not null,clue_id,null))
            when grouping(hour)=0
            then count(if(clue_rn=1,clue_id,null))
            else null end as clue_total_count
from tmp
group by
grouping sets(
       ( hour,origin_type,clue_state)
);
"